{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开相关的工作簿\n",
    "wb商城订单=xw.Book(r'j:\\王振洋资料\\1.商贸公司资料\\9月商贸公司资料\\技术入账\\赠品、推荐币订单_出库57-70,15#.xlsx')\n",
    "wb基础数据=xw.Book(r'J:\\王振洋资料\\1.商贸公司资料\\t+基础数据-技术.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 读取商城订单作为原始数据源 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "商城订单=wb商城订单.sheets('赠品、推荐币订单').range('a1').expand().options(pd.DataFrame).value\n",
    "商城订单['合计支付金额']=商城订单['在线支付时价格']+商城订单['混合支付时的在线支付金额']\n",
    "商城订单=商城订单.groupby(by=['城市名称'])['合计支付金额'].sum().reset_index()\n",
    "商城订单['城市名称']=商城订单['城市名称'].str.replace('市','')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 读取基础数据中的部门档案、项目档案等，进行匹配"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "部门档案=wb基础数据.sheets('部门档案').range('a1').expand().options(pd.DataFrame).value.reset_index()\n",
    "项目档案=wb基础数据.sheets('项目档案').range('a1').expand().options(pd.DataFrame).value.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 匹配部门\n",
    "merge=pd.merge(商城订单,部门档案,left_on='城市名称',right_on='城市',how='left')\n",
    "# 匹配项目\n",
    "merge=pd.merge(merge,项目档案,left_on='部门名称',right_on='项目名称',how='left')\n",
    "'''因为项目和 部门的名字都是直营xxx，渠道xxx等，所以用部门名称来匹配项目名称'''\n",
    "merge=merge.loc[:,['城市名称','合计支付金额','部门编码','部门名称','项目编码','项目名称']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb商城订单.sheets('编码匹配中间表').cells.clear()\n",
    "wb商城订单.sheets('编码匹配中间表').cells.number_format='@'\n",
    "wb商城订单.sheets('编码匹配中间表').range('a1').value=merge"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
